# -*- coding: utf-8 -*-

from scrapy import signals
import json, time
import codecs

from twisted.enterprise import adbapi
from datetime import datetime
from hashlib import md5
import MySQLdb
import MySQLdb.cursors


class MySQLPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbargs = dict(
            host=settings['MYSQL_SERVER'],
            db=settings['MYSQL_DB'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASS'],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        dbpool = adbapi.ConnectionPool('MySQLdb', **dbargs)
        return cls(dbpool)

    def process_item(self, item, spider):
        d = self.dbpool.runInteraction(self._do_upinsert, item, spider)
        d.addErrback(self._handle_error, item, spider)
        d.addBoth(lambda _: item)
        return d

    # 将每行更新或写入数据库中
    def _do_upinsert(self, conn, item, spider):
        now = datetime.utcnow().replace(microsecond=0).isoformat(' ')

        if item['table'] == 't_major':
            sql = "select 1 from t_major where code = '%s'" % item['code']

            conn.execute(sql)
            ret = conn.fetchone()

            print(ret)

            if ret != None:
                sql = "update t_major set name = '%s', url ='%s' type = %s, level = %s, age = '%s', diploma = '%s', course = '%s', intro = '%s', job = '%s', updated_at = '%s' where code = '%s'" % \
                      (item['name'], item['url'], int(item['type']), int(item['level']), item['age'], item['diploma'],
                       item['course'], item['intro'], item['job'], now, item['code'])

            else:
                sql = "insert into t_major (name, url, type, level, code, age, diploma, course, intro, job, created_at, updated_at) values('%s', '%s', %s, %s, '%s', '%s', '%s', \'%s\', \'%s\', \'%s\', '%s', '%s')" % \
                      (item['name'], item['url'], int(item['type']), int(item['level']), item['code'], item['age'],
                       item['diploma'], item['course'], item['intro'], item['job'], now, now)

                # print("========"*20)
                # print(sql)
                # print("========"*20)

            conn.execute(sql)

        elif item['table'] == 't_university':
            fid = item['fid']

            sql = "select 1 from t_university where fid = '%s'" % fid
            conn.execute(sql)
            ret = conn.fetchone()

            del item['table']

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_university set " + set[0:len(set) - 1] + " where fid='%s'" % fid
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += '"%s",' % (str(value).replace('"', "'"))
                sql = "INSERT INTO t_university (" + fields[0:len(fields) - 1] + ") Values (" + values[
                                                                                                0:len(values) - 1] + ")"

            print("++++" * 20)
            print(sql)
            print("++++" * 20)

            conn.execute(sql)

        elif item['table'] == 't_guide':
            fid = item['fid']
            type = item['type']

            sql = "select 1 from t_guide where fid = '%s' and type='%s'" % (fid, type)
            conn.execute(sql)
            ret = conn.fetchone()

            del item['table']

            if ret == None:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_guide (" + fields[0:len(fields) - 1] + ") Values (" + values[
                                                                                           0:len(values) - 1] + ")"

            conn.execute(sql)

        elif item['table'] == 't_university_point':
            del item['table']

            sql = "select 1 from t_university_point where fid = '%s' and name = '%s' and type='%s'" % (
            item['fid'], item['name'], item['type'])

            conn.execute(sql)
            ret = conn.fetchone()

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_university_point set " + set[0:len(set) - 1] + " where id='%s'" % ret['id']
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_university_point (" + fields[0:len(fields) - 1] + ") Values (" + values[0:len(
                    values) - 1] + ")"

            conn.execute(sql)

        elif item['table'] == 't_university_major':
            del item['table']

            sql = "select 1 from t_university_major where fid = '%s' and name = '%s' and type='%s'" % (
            item['fid'], item['name'], item['type'])

            conn.execute(sql)
            ret = conn.fetchone()

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_university_major set " + set[0:len(set) - 1] + " where id='%s'" % ret['id']
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_university_major (" + fields[0:len(fields) - 1] + ") Values (" + values[0:len(
                    values) - 1] + ")"

            conn.execute(sql)

        elif item['table'] == 't_university_history_data':
            del item['table']

            sql = "select 1 from t_university_history_data where fid = '%s' and provinceid = '%s' and type='%s' and year='%s' and batch='%s' and student='%s' and lower='%s'" % (
            item['fid'], item['provinceid'], item['type'], item['year'], item['batch'], item['student'], item['lower'])

            conn.execute(sql)
            ret = conn.fetchone()

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_university_history_data set " + set[0:len(set) - 1] + " where id='%s'" % ret['id']
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_university_history_data (" + fields[0:len(fields) - 1] + ") Values (" + values[
                                                                                                             0:len(
                                                                                                                 values) - 1] + ")"

            conn.execute(sql)

        elif item['table'] == 't_university_major_data':
            del item['table']

            sql = "select 1 from t_university_major_data where fid = '%s' and provinceid = '%s' and name='%s' and year='%s' and type='%s' and batch='%s'" % (
            item['fid'], item['provinceid'], item['name'], item['year'], item['type'], item['batch'])

            conn.execute(sql)
            ret = conn.fetchone()

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_university_major_data set " + set[0:len(set) - 1] + " where id='%s'" % ret['id']
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_university_profession_data (" + fields[0:len(fields) - 1] + ") Values (" + values[
                                                                                                                0:len(
                                                                                                                    values) - 1] + ")"

            conn.execute(sql)

        elif item['table'] == 't_admission':
            del item['table']

            sql = "select 1 from t_admission where provinceid = '%s' and type='%s' and year='%s' and name='%s'" % (
            item['provinceid'], item['type'], item['year'], item['name'])

            conn.execute(sql)
            ret = conn.fetchone()

            if ret:
                set = ''
                for key, value in item.items():
                    set += "%s = '%s'," % (key, value)
                sql = "update t_admission set " + set[0:len(set) - 1] + " where id='%s'" % ret['id']
            else:
                fields = ''
                values = ''
                for key, value in item.items():
                    fields += "%s," % (key)
                    values += "'%s'," % (value)
                sql = "INSERT INTO t_admission (" + fields[0:len(fields) - 1] + ") Values (" + values[
                                                                                               0:len(values) - 1] + ")"

            conn.execute(sql)
        elif item['table'] == 't_scholarship':
            sql = "select id from t_scholarship where name = '%s' and title='%s'" % (item['name'], item['title'])

            conn.execute(sql)
            ret = conn.fetchone()
            if ret != None:
                sql = "update t_scholarship set name = '%s', title = %s, amount = %s, standard = '%s', people = '%s', updated_at = '%s' where id = '%s'" % \
                      (item['name'], item['title'], item['amount'], item['standard'], item['people'], now, ret['id'])

                print("------" * 20)
                print(sql)

            else:
                print("++++++" * 20)
                sql = "insert into t_scholarship (name, title, amount, standard, people, created_at, updated_at) values('%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
                      (item['name'], item['title'], item['amount'], item['standard'], item['people'], now, now)
                print(sql)
        elif item['table'] == 't_university_student_plan':
            sql = "select id from t_university_student_plan where provinceid = '%s' and bk_name='%s' and type='%s' and year='%s' and major='%s' and count='%s'" % \
                  (item['provinceid'], item['bk_name'], item['type'], item['year'], item['major'], item['count'])

            conn.execute(sql)
            ret = conn.fetchone()
            if ret != None:
                sql = "update t_university_student_plan set fid = '%s', bk_name = %s, bk_id = %s, provinceid = '%s', type = '%s', year = '%s', major='%s', code='%s', count='%s', updated_at='%s' where id = '%s'" % \
                      (item['fid'], item['bk_name'], item['bk_id'], item['provinceid'], item['type'], item['year'],
                       item['major'], item['code'], item['count'], now, ret['id'])

            else:
                sql = "insert into t_university_student_plan (fid, bk_name, bk_id, provinceid, type, year, major, code, count, created_at, updated_at) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
                      (item['fid'], item['bk_name'], item['bk_id'], item['provinceid'], item['type'], item['year'],
                       item['major'], item['code'], item['count'], now, now)
            conn.execute(sql)

        elif item['table'] == 't_bk_university':

            sql = "select id from t_bk_university where cid='%s'" % item['cid']

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into t_bk_university (name, cid, created_at, updated_at) values( '%s', '%s', '%s', '%s')" % (
                item['name'], item['cid'], now, now)
            conn.execute(sql)


        elif item['table'] == 't_guancha':
            sql = "select id from t_guancha where title='%s'" % item['title']

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into t_guancha (title, content) values( '%s', '%s')" % (item['title'], item['content'])
            conn.execute(sql)

        elif item['table'] == 'scrapy_province':
            sql = "select id from scrapy_province where name='%s'" % item['name']

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into scrapy_province (name, number) values( '%s', '%s')" % (item['title'], item['number'])
            conn.execute(sql)
        elif item['table'] == 'scrapy_university':
            sql = "select id from scrapy_university where name='%s'" % item['name']

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into scrapy_university (name, url) values( '%s', '%s')" % (item['name'], item['url'])
            conn.execute(sql)
        elif item['table'] == 'scrapy_university_update':
            sql = "select id from scrapy_university where id='%d'" % item['id']
            conn.execute(sql)
            ret = conn.fetchone()
            if ret:
                sql = "update scrapy_university set sch_id = '%s',diploma = '%s' where id = '%s'" % \
                      (item['sch_id'], item['diploma'], item['id'])
                conn.execute(sql)
        elif item['table'] == 'scrapy_score_line':
            sql = "select id from scrapy_score_line where university_name='%s' and " \
                  "province_name='%s' and year='%s' and type='%s'" \
                  "" % (item['university_name'], item['province_name'], item['year'], item['type'])

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into scrapy_score_line (university_name, province_name, year, average_score," \
                      " minimum_score, provincial_line, minimum_score_ranking, people_number, batch, type) " \
                      "values( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (item['university_name'],
                       item['province_name'], item['year'], item['average_score'], item['minimum_score'], item['provincial_line']
                       , item['minimum_score_ranking'], item['people_number'], item['batch'], item['type'])
                conn.execute(sql)
        elif item['table'] == 'scrapy_score':
            sql = "select id from scrapy_score where sch_id='%s' and number='%s'   and type='%s'  " \
                  "" % (item['sch_id'], item['number'], item['type'])

            conn.execute(sql)
            ret = conn.fetchone()
            if ret == None:
                sql = "insert into scrapy_score (name, sch_id, diploma, province_name," \
                      " number, type) " \
                      "values( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (
                      item['name'],
                      item['sch_id'], item['diploma'], item['province_name'], item['number'],
                      item['type'])
                conn.execute(sql)
    # 获取url的md5编码
    def _get_linkmd5id(self, item):
        # url进行md5处理，为避免重复采集设计
        return md5(item['link']).hexdigest()

    # 异常处理
    def _handle_error(self, failure, item, spider):
        log.err(failure)
